package sql;

import java.sql.*;
import java.util.*;
import bean.BookBean;

public class BookLookUp 
{
	DataAccess dataAccess = new DataAccess();	
	
	public HashMap<Integer, BookBean> getBookInfo(String isbn, String authorFirstName, 
												String authorLastName, String title, String keyword) throws ClassNotFoundException, SQLException
	{
		HashMap<Integer, BookBean> bookList = new HashMap<Integer, BookBean>();
		String query = null;
		String commonSql = "SELECT BOOK_ID, ISBN, TITLE, AUTHOR_FIRST_NAME, AUTHOR_LAST_NAME, PUBLISHER, COPYRIGHT, NUM_OF_PAGES," +
		  				         " EDITION, DESCRIPTION, BOOK_CONDITION_CODE, BOOK_CATEGORY_CODE, PRICE, IS_SOLD, SELLER_ID"+
                            " FROM BOOK "+                  
                           " WHERE IS_SOLD = 'N'";
		
		if(isbn!= null && isbn.trim().length()!= 0)
		{		
			query = commonSql + " AND ISBN = '" +isbn +"'";
		}
		else if(authorFirstName!= null && authorFirstName.trim().length()!= 0
				&& authorLastName!=null && authorLastName.trim().length()!= 0)
		{
			query = commonSql +
	                " AND UPPER(TRIM(AUTHOR_FIRST_NAME)) = UPPER(TRIM('" +authorFirstName +"'))" +
					" AND UPPER(TRIM(AUTHOR_LAST_NAME)) = UPPER(TRIM('" +authorLastName +"'))";
		}
		else if(title!= null && title.trim().length()!= 0)
		{
			query = commonSql +
	  				" AND UPPER(TRIM(TITLE)) = UPPER(TRIM('" +title +"'))";
		}
		else if(keyword!= null && keyword.trim().length()!= 0)
		{
			query = commonSql +
		  			" AND UPPER(TITLE) LIKE UPPER('%" +keyword +"%')" +
	  				" OR UPPER(DESCRIPTION) LIKE UPPER('%" +keyword +"%')";
		}
		
		ResultSet rset = dataAccess.getSelectResult(query);
		
		while(rset.next())
		{
			BookBean book = new BookBean();
			book.setBookId(rset.getInt(1));
			book.setIsbn(rset.getString(2));
			book.setTitle(rset.getString(3));
			book.setAuthorFirstName(rset.getString(4));
			book.setAuthorLastName(rset.getString(5));
			book.setPublisher(rset.getString(6));
			book.setCopyright(rset.getInt(7));
			book.setNumOfPages(rset.getInt(8));
			book.setEdition(rset.getString(9));
			book.setDescription(rset.getString(10));
			book.setBookConditionCode(rset.getString(11));
			book.setBookCategoryCode(rset.getString(12));
			book.setPrice(rset.getDouble(13));
			book.setIsSold(rset.getString(14));
			book.setSellerId(rset.getInt(15));
			
			bookList.put(book.getBookId(), book);
		}
		
		return bookList;		
	}
}
